﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DTO;
namespace DAO
{
    public class NhaCungCapDAO
    {
        public List<NhaCungCapDTO> LayDSNhaCungCap()
        {
            List<NhaCungCapDTO> dsNCC = new List<NhaCungCapDTO>();
            SqlConnection cn = DataProvider.ConnectionData();
            string sql = "sp_LayDanhSachNhaCungCap";
            SqlCommand cmd = new SqlCommand(sql, cn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader dr = cmd.ExecuteReader();
            NhaCungCapDTO ncc;
            while (dr.Read())
            {
                ncc = new NhaCungCapDTO();
                ncc.MaNCC = int.Parse(dr["MaNCC"].ToString());
                ncc.TenNCC = dr["TenNCC"].ToString();
                ncc.DiaChi = dr["DiaChi"].ToString();
                ncc.SDT = dr["SDT"].ToString();
                ncc.WebSite = dr["WebSite"].ToString();
                ncc.Email = dr["Email"].ToString();
                ncc.TaiKhoan = dr["TaiKhoan"].ToString();
                dsNCC.Add(ncc);
            }
            cn.Close();
            return dsNCC;
        }
       

        public DataTable LayDSNCC()
        {
            DataTable dt = new DataTable();
            SqlConnection cn = DataProvider.ConnectionData();
            SqlCommand cmd = new SqlCommand("sp_LayDanhSachNhaCungCap", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            cn.Close();
            return dt;
        }
        public void ThemNhaCungCap(NhaCungCapDTO nccdto)
        {
            SqlConnection cn = DataProvider.ConnectionData();
            SqlCommand cmd = new SqlCommand("sp_ThemNCC", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@TenNCC", SqlDbType.NVarChar);
            cmd.Parameters.Add("@DiaChi", SqlDbType.NVarChar);
            cmd.Parameters.Add("@SDT", SqlDbType.NVarChar);
            cmd.Parameters.Add("@WebSite", SqlDbType.VarChar);
            cmd.Parameters.Add("@Email", SqlDbType.VarChar);
            cmd.Parameters.Add("@TaiKhoan", SqlDbType.VarChar);
            //gan gia tri cho tham so
            cmd.Parameters["@TenNCC"].Value = nccdto.TenNCC;
            cmd.Parameters["@DiaChi"].Value = nccdto.DiaChi;
            cmd.Parameters["@SDT"].Value = nccdto.SDT;
            cmd.Parameters["@WebSite"].Value = nccdto.WebSite;
            cmd.Parameters["@Email"].Value = nccdto.Email;
            cmd.Parameters["@TaiKhoan"].Value = nccdto.TaiKhoan;

            //thuc thi truy van
            cmd.ExecuteNonQuery();
            cn.Close();
        }
        public void CapNhatNCC(NhaCungCapDTO nccdto)
        {
            SqlConnection cn = DataProvider.ConnectionData();
            SqlCommand cmd = new SqlCommand("sp_CapNhatNCC", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@MaNCC", nccdto.MaNCC);
            cmd.Parameters.Add("@TenNCC", nccdto.TenNCC);
            cmd.Parameters.Add("@DiaChi", nccdto.DiaChi);
            cmd.Parameters.Add("@SDT", nccdto.SDT);
            cmd.Parameters.Add("@WebSite", nccdto.WebSite);
            cmd.Parameters.Add("@Email", nccdto.Email);
            cmd.Parameters.Add("@TaiKhoan", nccdto.TaiKhoan);
            
            cmd.ExecuteNonQuery();
            cn.Close();
        }


        public void XoaNCC(NhaCungCapDTO nccdto)
        {
            SqlConnection cn = DataProvider.ConnectionData();
            SqlCommand cmd = new SqlCommand("sp_XoaNCC", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@MaNCC", SqlDbType.NVarChar);
            cmd.Parameters["@MaNCC"].Value = nccdto.MaNCC;
            cmd.ExecuteNonQuery();
            cn.Close();
        }
        public DataTable TimNCC(string TenNCC, string SoDT, string Email)
        {
            DataTable dt = new DataTable();
            SqlConnection cn = DataProvider.ConnectionData();
            SqlCommand cmd = new SqlCommand("sp_TimKiemNCC", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@TenNCC", SqlDbType.NVarChar);
            cmd.Parameters.Add("@SoDT", SqlDbType.NVarChar);
            cmd.Parameters.Add("@Email", SqlDbType.NChar);

            cmd.Parameters["@TenNCC"].Value = TenNCC;
            cmd.Parameters["@SoDT"].Value = SoDT;
            cmd.Parameters["@Email"].Value = Email;


            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            cn.Close();
            return dt;
        }


    }
}
